﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data.Sql;

namespace RestaurantManagerWebApp.Pages.UserManagement
{
    public partial class EmployeeManagementUpdateDelete : System.Web.UI.Page
    {
        string connection = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                GridView1.DataBind();
                GridView1.Columns[13].Visible = false;
            }
        }

        protected void lblDelete_Click(object sender, EventArgs e)
        {

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridViewRow row = GridView1.SelectedRow;
            txtUserID.Text = row.Cells[0].Text;
            lblShowEmpID.Text = row.Cells[1].Text;
            lblShowEmpID.Visible = true;
            txtFname.Text = row.Cells[2].Text;
            txtLname.Text = row.Cells[3].Text;
            calendar.SelectedDate = Convert.ToDateTime(row.Cells[4].Text);
            txtDOB.Text = calendar.SelectedDate.ToString("dd/MM/yyyy");
            txtHP.Text = row.Cells[5].Text.Replace("&nbsp;", "");
            txtAddr.Text = row.Cells[6].Text.Replace("&nbsp;", "");
            txtEmail.Text = row.Cells[7].Text.Replace("&nbsp;", "");
            txtBankAcc.Text = row.Cells[8].Text.Replace("&nbsp;", "");
            ddlNationality.SelectedValue = row.Cells[9].Text;
            calJoinDate.SelectedDate = Convert.ToDateTime(row.Cells[10].Text);
            ddlRoles.SelectedValue = row.Cells[11].Text;
            txtJDate.Text = calendar.SelectedDate.ToString("dd/MM/yyyy");
            SqlConnection conn = new SqlConnection(connection);
            conn.Open();
            string empID = row.Cells[1].Text;
            string query = "SELECT p_name, pathname FROM Photos where emp_id = @empID";
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@empID", empID);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtpname.Text = dr["p_name"].ToString();
                Image1.ImageUrl = dr["pathname"].ToString();
            }
            //string originalPassword = ((HiddenField)GridView1.SelectedRow.FindControl("HiddenField1")).Value; 


        }

        private static SqlDataReader CheckDupUserID(SqlConnection conn, string userID)
        {
            string cmpUserID = "Select id_no from Emp_records where id_no =@cmpUserID";
            SqlCommand checkU = new SqlCommand(cmpUserID, conn);
            checkU.Parameters.AddWithValue("@cmpUserID", userID);
            SqlDataReader dr = checkU.ExecuteReader();
            return dr;
        }

        private static SqlDataReader CheckDupEMPUserID(SqlConnection conn, string empID, string userID)
        {
            string cmpUserID = "Select id_no, emp_id from Emp_records where id_no =@cmpUserID AND emp_id = @cmpEMPID";
            SqlCommand checkU = new SqlCommand(cmpUserID, conn);
            checkU.Parameters.AddWithValue("@cmpEMPID", empID);
            checkU.Parameters.AddWithValue("@cmpUserID", userID);
            SqlDataReader dr = checkU.ExecuteReader();
            return dr;
        }

        private static SqlDataReader CheckFkEMPID(SqlConnection conn, string empID)
        {
            string cmpEMPID = "Select emp_id from Users where emp_id =@cmpEmpID";
            SqlCommand checkU = new SqlCommand(cmpEMPID, conn);
            checkU.Parameters.AddWithValue("@cmpEmpID", empID);
            SqlDataReader dr = checkU.ExecuteReader();
            return dr;
        }

        private void ClearForm()
        {
            txtAddr.Text = "";
            txtBankAcc.Text = "";
            txtEmail.Text = "";
            lblShowEmpID.Text = "";
            lblShowEmpID.Visible = false;
            txtFname.Text = "";
            txtHP.Text = "";
            txtLname.Text = "";
            txtUserID.Text = "";
            calJoinDate.SelectedDates.Clear();
            calendar.SelectedDates.Clear();
            txtPassword.Text = "";
            Image1.ImageUrl = "";
            txtpname.Text = "";
            ddlRoles.ClearSelection();
            ddlNationality.ClearSelection();
            txtDOB.Text = "";
            txtJDate.Text = "";
        }

        protected void lblUpdate_Click(object sender, EventArgs e)
        {
            if (GridView1.SelectedValue != null)
            {

                SqlConnection conn = new SqlConnection(connection);
                conn.Open();
                string addr = txtAddr.Text;
                string bankAcc = txtBankAcc.Text;
                string email = txtEmail.Text;
                string empID = lblShowEmpID.Text;
                string fname = txtFname.Text;
                string hpNo = txtHP.Text;
                string lname = txtLname.Text;
                string userID = txtUserID.Text;
                string nationality = ddlNationality.SelectedValue.ToString();
                string password = txtPassword.Text;
                string role = ddlRoles.SelectedValue.ToString();
                DateTime joinedDate = calJoinDate.SelectedDate;
                DateTime dob = calendar.SelectedDate;


                SqlDataReader dr = CheckDupEMPUserID(conn, empID, userID);
                if (dr.HasRows)
                {
                    dr.Close();
                    UpdateRecordsExEmpUser(conn, addr, bankAcc, email, empID, fname, hpNo, lname, nationality, joinedDate, dob);
                    if (password == "")
                    {
                        GridViewRow row = GridView1.SelectedRow;
                        UpdateEXUserRecordsWithoutPassword(conn, role, empID);

                        string fileName = txtpname.Text;
                        string uploadFolderPath = Image1.ImageUrl.ToString();
                        UpdatePhotoRecord(conn, fileName, uploadFolderPath, empID);


                    }
                    else
                    {
                        password = txtPassword.Text;
                        UpdateEXUserRecords(conn, role, password, empID);

                        string fileName = txtpname.Text;
                        string uploadFolderPath = Image1.ImageUrl.ToString();
                        UpdatePhotoRecord(conn, fileName, uploadFolderPath, empID);

                    }
                    Response.Write("<script type=\"text/javascript\">alert('Employee Record Updated!');</script>");
                    GridView1.DataBind();
                    ClearForm();
                }
                else
                {
                    dr.Close();
                    SqlDataReader dr1 = CheckDupUserID(conn, userID);
                    if (dr1.HasRows)
                    {
                        Response.Write("<script type=\"text/javascript\">alert('Unable to Update Duplicate User ID!');</script>");

                    }
                    else
                    {
                        dr1.Close();
                        UpdateRecords(conn, addr, bankAcc, email, empID, fname, hpNo, lname, userID, nationality, joinedDate, dob);
                        if (password == "")
                        {
                            GridViewRow row = GridView1.SelectedRow;
                            password = ((HiddenField)GridView1.SelectedRow.FindControl("HiddenField1")).Value;
                            UpdateUserRecords(conn, role, password, userID, empID);


                            string fileName = txtpname.Text;
                            string uploadFolderPath = Image1.ImageUrl.ToString();
                            UpdatePhotoRecord(conn, fileName, uploadFolderPath, empID);

                        }
                        else
                        {
                            password = txtPassword.Text;
                            UpdateUserRecords(conn, role, password, userID, empID);

                            string fileName = txtpname.Text;
                            string uploadFolderPath = Image1.ImageUrl.ToString();
                            UpdatePhotoRecord(conn, fileName, uploadFolderPath, empID);

                        }

                        Response.Write("<script type=\"text/javascript\">alert('Employee Record Updated!');</script>");
                        GridView1.DataBind();
                        ClearForm();

                    }
                }


                conn.Close();

            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('Please Select');", true);
                //Response.Write("<script type=\"text/javascript\">alert('Please select a Employee Record to Update!');</script>");
            }

        }

        private static void UpdateRecords(SqlConnection conn, string addr, string bankAcc, string email, string empID, string fname, string hpNo, string lname, string userID, string nationality, DateTime joinedDate, DateTime dob)
        {
            string UpdateQuery = "UPDATE Emp_records SET address=@addr, bank_acc=@bankAcc, email=@email, fname=@fname, hp_no=@hpNo, lname=@lname, id_no=@userID, nationality=@nationality, when_joined=@joinedDate, dob=@dob WHERE emp_id=@empID";
            SqlCommand cmd = new SqlCommand(UpdateQuery, conn);
            cmd.Parameters.AddWithValue("@addr", addr);
            cmd.Parameters.AddWithValue("@bankAcc", bankAcc);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@empID", empID);
            cmd.Parameters.AddWithValue("@fname", fname);
            cmd.Parameters.AddWithValue("@hpNo", hpNo);
            cmd.Parameters.AddWithValue("@lname", lname);
            cmd.Parameters.AddWithValue("@userID", userID);
            cmd.Parameters.AddWithValue("@nationality", nationality);
            cmd.Parameters.AddWithValue("@joinedDate", joinedDate);
            cmd.Parameters.AddWithValue("@dob", dob);
            cmd.ExecuteNonQuery();
        }

        private static void UpdateRecordsExEmpUser(SqlConnection conn, string addr, string bankAcc, string email, string empID, string fname, string hpNo, string lname, string nationality, DateTime joinedDate, DateTime dob)
        {
            string UpdateQuery = "UPDATE Emp_records SET address=@addr, bank_acc=@bankAcc, email=@email, fname=@fname, hp_no=@hpNo, lname=@lname, nationality=@nationality, when_joined=@joinedDate, dob=@dob WHERE emp_id=@empID";
            SqlCommand cmd = new SqlCommand(UpdateQuery, conn);
            cmd.Parameters.AddWithValue("@addr", addr);
            cmd.Parameters.AddWithValue("@bankAcc", bankAcc);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@empID", empID);
            cmd.Parameters.AddWithValue("@fname", fname);
            cmd.Parameters.AddWithValue("@hpNo", hpNo);
            cmd.Parameters.AddWithValue("@lname", lname);
            cmd.Parameters.AddWithValue("@nationality", nationality);
            cmd.Parameters.AddWithValue("@joinedDate", joinedDate);
            cmd.Parameters.AddWithValue("@dob", dob);
            cmd.ExecuteNonQuery();
        }

        private static void UpdateEXUserRecords(SqlConnection conn, string role, string password, string empID)
        {
            string UpdateQuery = "UPDATE Users SET role=@role, password=@password WHERE emp_id=@empID";
            SqlCommand cmd = new SqlCommand(UpdateQuery, conn);
            cmd.Parameters.AddWithValue("@role", role);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@empID", empID);


            cmd.ExecuteNonQuery();
        }

        private static void UpdateEXUserRecordsWithoutPassword(SqlConnection conn, string role, string empID)
        {
            string UpdateQuery = "UPDATE Users SET role=@role WHERE emp_id=@empID";
            SqlCommand cmd = new SqlCommand(UpdateQuery, conn);
            cmd.Parameters.AddWithValue("@role", role);
            cmd.Parameters.AddWithValue("@empID", empID);


            cmd.ExecuteNonQuery();
        }

        private static void UpdateUserRecords(SqlConnection conn, string role, string password, string userID, string empID)
        {
            string UpdateQuery = "UPDATE Users SET role=@role, password=@password, u_id = @userID WHERE emp_id=@empID";
            SqlCommand cmd = new SqlCommand(UpdateQuery, conn);
            cmd.Parameters.AddWithValue("@role", role);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@empID", empID);
            cmd.Parameters.AddWithValue("@userID", userID);

            cmd.ExecuteNonQuery();
        }

        private void UpdatePhotoRecord(SqlConnection conn, string pname, string pathname, string empID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Update Photos Set p_name=@pname, pathname=@pathname where emp_id=@empID";
                cmd.Parameters.AddWithValue("@pname", pname);
                cmd.Parameters.AddWithValue("@empID", empID);
                cmd.Parameters.AddWithValue("@pathname", pathname);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Response.Write(ex.Message);
            }
        }

        protected void calJoinDate_SelectionChanged(object sender, EventArgs e)
        {
            calJoinDate.Visible = false;
            txtJDate.Text = calJoinDate.SelectedDate.ToString("dd/MM/yyyy");
        }

        protected void calendar_SelectionChanged(object sender, EventArgs e)
        {
            calendar.Visible = false;
            txtDOB.Text = calendar.SelectedDate.ToString("dd/MM/yyyy");
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string fileName = FileUpload1.FileName.ToString();
                string uploadFolderPath = "~/Images/";
                string filePath = HttpContext.Current.Server.MapPath(uploadFolderPath);
                FileUpload1.SaveAs(filePath + "\\" + fileName);
                Image1.ImageUrl = "~/Images/" + "/" + FileUpload1.FileName.ToString();

            }
        }



        protected void btnShowDOB_Click(object sender, EventArgs e)
        {
            if (calendar.Visible == true)
            {
                calendar.Visible = false;
            }
            else
            {
                calendar.Visible = true;
            }
            txtDOB.Focus();
        }

        protected void btnShowJDate_Click(object sender, EventArgs e)
        {
            if (calJoinDate.Visible == true)
            {
                calJoinDate.Visible = false;
            }
            else
            {
                calJoinDate.Visible = true;
            }
            txtJDate.Focus();
        }

        protected void lblCancelPic_Click(object sender, EventArgs e)
        {
            Image1.ImageUrl = "";
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            //Call bind gridview function
            GridView1.DataBind();
        }

        protected void lblCancel_Click(object sender, EventArgs e)
        {
            ClearForm();
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (GridView1.SelectedValue != null)
            {

                SqlConnection conn = new SqlConnection(connection);
                conn.Open();
                string empID = lblShowEmpID.Text;
                string deleteUser = "Delete FROM Users where emp_id = @empID";
                SqlCommand cmd = new SqlCommand(deleteUser, conn);
                cmd.Parameters.AddWithValue("@empID", empID);
                SqlDataReader dr1 = cmd.ExecuteReader();
                dr1.Close();
                string deleteEMP = "Delete FROM Emp_records where emp_id = @empID";
                SqlCommand cmd2 = new SqlCommand(deleteEMP, conn);
                cmd2.Parameters.AddWithValue("@empID", empID);
                SqlDataReader dr2 = cmd2.ExecuteReader();
                //Response.Write("<script type=\"text/javascript\">alert('User Successfully Deleted!');</script>");
                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('User Successfully Deleted');", true);
                GridView1.EditIndex = -1;
                GridView1.DataBind();
                ClearForm();



                conn.Close();
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('Please Select');", true);
                //Response.Write("<script type=\"text/javascript\">alert('Please select a Employee Record to Update!');</script>");
            }
        }
    }
}